package com.g2room.myuall.temp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.g2room.myuall.grap.univ.po.UnivPo;
import com.g2room.myuall.util.DbUtil;

public class Update {
	static Map<String, String> provUrl = new HashMap<String, String>();
	public static void main(String[] args) {
//		update();
//		compareSchool();
		createProvHtml();
	}
	/**
	 * 生成省份html片段
	 */
	public static void createProvHtml() {
		StringBuffer sb = new StringBuffer();
//		<li><b>A</b><a href="javascript:void(0)" id="1000010">安徽</a></li>
		List<SysArea> areas = getArea();
		for(SysArea area : areas) {
			sb.append("<li><b>"+area.getSortWord()+"</b><a href=\"javascript:void(0);\" id=\""+area.getId()+"\">"+area.getName()+"</a></li>\n");
		}
		System.out.println(sb.toString());
	}
	public static void update() {
//		Map<Integer, String> its= getProperty();
//		for(Integer id : its.keySet()) {
//			updateUnivInfoDb(id, its.get(id));
//		}
//		Map<Integer, String> its= getArea();
//		updateProvinceDb("", its);
//		updateProvinceDb("univ_major_score", its);
//		updateUnivRankDb(its);
//		List<UnivPo> univs = getSchool();
//		updateSchoolNameDb("univ_enrollment", univs);
//		updateSchoolIdDb("univ_major_score", univs);
		
		
//		updateOtherDb();
		
		List<UnivPo> majors = getMajor();
		updateMajorIdDb("univ_major_score",majors);
//		updateMajorIdDb("major_rank",majors);
//		updateMajorIdDb("univ_major",majors);
	}

	public static void updateOtherDb() {
		try {
			List<String> sqls = new ArrayList<String>();
			sqls.add("update univ_major_score set batch_id=87,batch_name='本科一批' where batch_name='一批'");
			sqls.add("update univ_major_score set batch_id=90,batch_name='本科二批' where batch_name='二批'");
			sqls.add("update univ_major_score set batch_id=88,batch_name='本科三批' where batch_name='三批'");
			sqls.add("update univ_major_score set batch_id=89 where batch_name='专科'");
			sqls.add("update univ_major_score set batch_id=91 where batch_name='提前'");
			for(String sql : sqls) {
				PreparedStatement ps = DbUtil.getConn().prepareStatement(sql);
				ps.execute();
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	public static void updateProvinceDb(String table, Map<Integer, String> areas) {
		try {
			String sql = "update "+table+" set province_id=? where province_name=?";
			PreparedStatement ps = DbUtil.getConn().prepareStatement(sql);
			for(Integer id : areas.keySet()) {
				ps.setInt(1, id);
				ps.setString(2, areas.get(id));
				ps.addBatch();
			}
			ps.executeBatch();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	public static void updateSchoolIdDb(String table, List<UnivPo> univs) {
		try {
			String sql = "update "+table+" set school_id=? where old_school_id=?";
			PreparedStatement ps = DbUtil.getConn().prepareStatement(sql);
			for(UnivPo univ : univs) {
				ps.setInt(1, univ.getId());
				ps.setInt(2, univ.getOldSchoolId());
				ps.addBatch();
			}
			ps.executeBatch();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	public static void updateBySchoolNameDb(String table, List<UnivPo> univs) {
		try {
			String sql = "update "+table+" set school_id=? where school_name=?";
			PreparedStatement ps = DbUtil.getConn().prepareStatement(sql);
			for(UnivPo univ : univs) {
				ps.setInt(1, univ.getId());
				ps.setString(2, univ.getName());
				ps.addBatch();
			}
			ps.executeBatch();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	public static void updateSchoolNameDb(String table, List<UnivPo> univs) {
		try {
			String sql = "update "+table+" set school_name=? where school_id=?";
			PreparedStatement ps = DbUtil.getConn().prepareStatement(sql);
			for(UnivPo univ : univs) {
				ps.setInt(2, univ.getId());
				ps.setString(1, univ.getName());
				ps.addBatch();
			}
			ps.executeBatch();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	/*
	 * 使用了UnivPo的id和name字段
	 */
	public static void updateMajorIdDb(String table, List<UnivPo> univs) {
		try {
			String sql = "update "+table+" set major_id=? where major_name=?";
			PreparedStatement ps = DbUtil.getConn().prepareStatement(sql);
			List<String> nullMajors = getNullMajor();
			for(UnivPo univ : univs) {
				if(nullMajors.contains(univ.getName())) {
					System.out.println(univ.getId()+"==" + univ.getName());
					ps.setInt(1, univ.getId());
					ps.setString(2, univ.getName());
					ps.addBatch();
				}
			}
			ps.executeBatch();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	public static List<String> getNullMajor() {
		List<String> its = new ArrayList<String>();
		try {
			Connection connection = DbUtil.getConn();
			Statement statement = connection.createStatement();
			String sql = "select major_name from univ_major_score where major_id is null group by major_name";
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()) {
				its.add(rs.getString("major_name"));
			}
			rs.close();
			statement.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return its;
	}
	public static List<UnivPo> getMajor() {
		List<UnivPo> its = new ArrayList<UnivPo>();
		try {
			Connection connection = DbUtil.getConn();
			Statement statement = connection.createStatement();
			String sql = "select id,name from major_info";
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()) {
				UnivPo univ = new UnivPo();
				univ.setId(rs.getInt("id"));
				univ.setName(rs.getString("name"));
				its.add(univ);
			}
			rs.close();
			statement.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return its;
	}
	public static void compareSchool() {
		Map<String, Integer> its = new HashMap<String, Integer>();
		Map<String, Integer> its1 = new HashMap<String, Integer>();
		try {
			Connection connection = DbUtil.getConn();
			Statement statement = connection.createStatement();
			String sql = "select id,name from univ_info";
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()) {
				its.put(rs.getString("name"), rs.getInt("id"));
			}
			sql = "select school_name,school_id from univ_major_score group by school_id";
			rs = statement.executeQuery(sql);
			while(rs.next()) {
				its1.put(rs.getString("school_name"), rs.getInt("school_id"));
			}
			for(String n : its1.keySet()) {
				Integer id = its1.get(n);
				Integer id1 = its.get(n);
				if(!id.equals(id1)) {
					System.out.println("name=="+n + ",id=="+id + ",id1=" +id1);
				}
			}
			rs.close();
			statement.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	public static List<UnivPo> getSchool() {
		List<UnivPo> its = new ArrayList<UnivPo>();
		try {
			Connection connection = DbUtil.getConn();
			Statement statement = connection.createStatement();
			String sql = "select id,name,old_school_id from univ_info";
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()) {
				UnivPo univ = new UnivPo();
				univ.setId(rs.getInt("id"));
				univ.setName(rs.getString("name"));
				univ.setOldSchoolId(rs.getInt("old_school_id"));
				its.add(univ);
			}
			rs.close();
			statement.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return its;
	}
	
	public static List<SysArea> getArea() {
		List<SysArea> its = new ArrayList<SysArea>();
		try {
			Connection connection = DbUtil.getConn();
			Statement statement = connection.createStatement();
//			String sql = "select id,name from sys_area where pid=0";
			String sql = "select sort_word,name,id from sys_area where sort_word is not null order by sort_word";
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()) {
				SysArea a = new SysArea();
				a.setSortWord(rs.getString("sort_word"));
				a.setName(rs.getString("name"));
				a.setId(rs.getInt("id"));
				its.add(a);
			}
			rs.close();
			statement.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return its;
	}
}
